/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.farmcode.plugin.objectinfo.jdbc;

import com.farmcode.api.runtime.FARMCodeContext;
import com.farmcode.api.runtime.metadata.DatabaseMetadataFARMCodeAPI;
import com.farmcode.api.runtime.metadata.Table;
import com.farmcode.api.runtime.metadata.TableSpc;
import com.farmcode.api.runtime.metadata.tables.Attribute;
import com.farmcode.api.runtime.metadata.tables.Constraint;
import com.farmcode.api.runtime.metadata.tables.ReferenceCollection;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author oiprado
 */
public class TablesDatabaseObjectInfo extends DatabaseInfo<Table, Table> {

  private String[] types = {"TABLE"};
  private java.sql.DatabaseMetaData databaseMetaData = null;

  public TablesDatabaseObjectInfo(FARMCodeContext context) throws ClassNotFoundException {
    super(context);
  }

  @Override
  public List<Table> explore() throws SQLException {
    List<Table> tables = new ArrayList<Table>();

    String schema = (String) context.get(FARMCodeContext.SCHEMA);
    databaseMetaData = new DatabaseMetadataFARMCodeAPI(getConnection());
    Connection connection = getConnection();
    databaseMetaData = connection.getMetaData();
    ResultSet resultSet = databaseMetaData.getTables(null, (schema).equals("") ? null : schema, null, types);
    while (resultSet.next()) {
      Table dbo = new TableSpc();
      String tableName = resultSet.getString(3); // table name (3)
      String tableType = resultSet.getString(4); // table type (4)
      if (!tableName.contains("$")) {
        dbo.setName(tableName);
        dbo.setType(tableType);
        tables.add(dbo);
      }
    }
    resultSet.close();
    return tables;
  }

  @Override
  public Table exploreChilds(Table dbo) throws SQLException {
    String schema = (String) context.get(FARMCodeContext.SCHEMA);
    Connection connection = getConnection();
    databaseMetaData = connection.getMetaData();
    ResultSet resultSet = databaseMetaData.getColumns(null, schema == null ? "" : schema, dbo.getName(), null);
    
    while (resultSet.next()) {
      String columnName = resultSet.getString("COLUMN_NAME");
      String type = resultSet.getString("TYPE_NAME");
      Integer dataType = resultSet.getInt("DATA_TYPE");
      String precision = resultSet.getString("COLUMN_SIZE");
      String isNullable = resultSet.getString("IS_NULLABLE");
      String scala = resultSet.getString("DECIMAL_DIGITS");
      String remarks = resultSet.getString("REMARKS");
      
      Attribute attribute = new Attribute();
      attribute.setName(columnName);
      attribute.setType(type);
      attribute.setPrecision(precision);
//      attribute.setIdentity(type.contains("identity"));
//      attribute.put("guid", "" + columnName.toLowerCase().contains("guid"));
//      attribute.put("pk", "false");
//      attribute.put("fk", "false");
//      attribute.put("scala", scala == null ? "0" : scala);
//      attribute.put("fktable", "none");
//      attribute.put("exptable", "none");
//      attribute.put("remarks", remarks);core-farmcode-server
      
//      attribute.put("native", getTypes().get(dataType).getNativeType());
//      attribute.put("datatype",  getTypes().get(dataType).getName());
//      attribute.put("nullable", isNullable.equals("YES") ? "true" : "false");
      dbo.getChilds().add(attribute);

    }

    getPrimaryKeys(dbo);
    getImportedKeys(dbo);
    getExportedKeys(dbo);
    getIndexInfo(dbo);
    return dbo;
  }
  

  private void getIndexInfo(Table dbo) throws SQLException {
    String schema = (String) context.get(FARMCodeContext.SCHEMA);
    ResultSet resultSet = databaseMetaData.getIndexInfo(null, schema == null ? "" : schema, dbo.getName(), true, false);
    while (resultSet.next()) {
      String column = resultSet.getString("COLUMN_NAME");
      String name = resultSet.getString("INDEX_NAME");
      if (column != null && name != null) {
        dbo.getConstraintChilds().add(new Constraint(name, column));
//        System.out.println(name);
//        System.out.println(column);
      }
    }
  }

  private void getPrimaryKeys(Table dbo) throws SQLException {
    String schema = (String) context.get(FARMCodeContext.SCHEMA);
    ResultSet resultSet = databaseMetaData.getPrimaryKeys(null, schema == null ? "" : schema, dbo.getName());
    while (resultSet.next()) {
      String name = resultSet.getString("COLUMN_NAME");
      lookUpProperty(dbo, name, "pk", "true");
    }
  }

  private void getImportedKeys(Table dbo) throws SQLException {
    String schema = (String) context.get(FARMCodeContext.SCHEMA);
    ResultSet resultSet = databaseMetaData.getImportedKeys(null, schema == null ? "" : schema, dbo.getName());
    while (resultSet.next()) {
      String name = resultSet.getString("FKCOLUMN_NAME");
      String fktable = resultSet.getString("PKTABLE_NAME");
      lookUpProperty(dbo, name, "fk", "true");
      lookUpProperty(dbo, name, "fkfield", name);
      lookUpProperty(dbo, name, "fktable", fktable);
    }
  }

  private void lookUpProperty(Table dbo, String name, String key, String value) {

    int length = dbo.getAttributeChilds().size();
    for (int index = 0; index < length; index++) {
      Attribute attribute = (Attribute) dbo.getAttributeChilds().get(index);
      if (attribute.getName().equals(name)) {
//        attribute.put(key, value);
        break;
      }
    }
  }

  private void getExportedKeys(Table dbo) throws SQLException {
    String schema = (String) context.get(FARMCodeContext.SCHEMA);
    ResultSet resultSet = databaseMetaData.getExportedKeys(null, schema == null ? "" : schema, dbo.getName());
    dbo.setReferenceChilds(new ReferenceCollection());
    while (resultSet.next()) {
      String name = resultSet.getString("FKCOLUMN_NAME");
      String exptable = resultSet.getString("FKTABLE_NAME");
      String pkTable = resultSet.getString("PKTABLE_NAME");
      dbo.getReferenceChilds().add(new com.farmcode.api.runtime.metadata.tables.Reference(exptable, name, pkTable));
    }
  }

}
